if exists (select 1
          from sysobjects
          where  id = object_id('bd_proc_dept_get_data')
          and type in ('P','PC'))
DROP PROCEDURE bd_proc_dept_get_data
GO
CREATE PROCEDURE bd_proc_dept_get_data
	@startDate as varchar(20),--查询开始时间
	@endDate as varchar(20),--查询结束时间
	@deptIds as varchar(max),--选择的部门ids
  @startRow as INT,--分页-开始行数
  @endRow as INT,--分页-结束行数
  @totalSize as INT OUTPUT
as
BEGIN

-- 关闭打印受影响行数
SET NOCOUNT ON;


-- 定义返回受影响行数结果
	declare @result int = 0;


	--  异常扑捉机制
		BEGIN TRY


			CREATE TABLE #data(
				dept_id int,
				card_date date,
				shift_id int,
				shift_bh varchar(200) COLLATE Chinese_PRC_CI_AS,
				swipe_begin int,
				swipe_end int
			)


			CREATE TABLE #result(
				deptId int,
				cardDate varchar(max) COLLATE Chinese_PRC_CI_AS,
				in_or_out int,
				sumUser int
			)


		-- 开启事务
			BEGIN TRAN



				declare @lstr1 varchar(max)
				declare @lstr2 varchar(max)
				declare @sqlstr varchar(max)
				declare @lstrCount nvarchar(max)
				declare @alterResult nvarchar(max);
				declare @col_name_str nvarchar(max);
				set @lstr1 = ' ';
				set @lstr2 = ' ';
				set @lstrCount = ' ';
				set @alterResult = '';
				set @col_name_str = ' ';
				set @totalSize = 100000

				select @lstr1=@lstr1 + '[' +t.shift_id + '] as ''col_'+t.bh+''',' ,@lstr2=@lstr2 + '[' +t.shift_id + '],' from (select distinct ltrim(str(shift_id)) as shift_id,mc,bh from bd_kq_timecard t INNER JOIN zt_banci z on t.shift_id = z.id where convert(varchar(10),card_date) BETWEEN @startDate and @endDate and dept_id in(select v.value from string_split(@deptIds,',')v) and t.shift_id>0) as t

				if @lstr1 !=' '
				BEGIN

					set @lstr1=left(@lstr1,len(@lstr1)-1)
					set @lstr2=left(@lstr2,len(@lstr2)-1)

					-- 封装数据
					insert into #data(dept_id,shift_id,shift_bh,card_date,swipe_begin,swipe_end)
					select dept_id, shift_id, bh, card_date, swipe_begin, swipe_end
					from bd_kq_timecard a
					inner join string_split(@deptIds,',') b on a.dept_id = b.value
					inner join zt_banci c on a.shift_id = c.id
					where isnull(shift_id,0) > 0 and card_date >= @startDate and card_date <= @endDate


					-- 拼接更新表结构
					select @alterResult += 'alter table #result add col_'+ isnull(shift_bh,'') + ' int; '
					from #data
					group by shift_bh


					-- 表结构更新
					exec (@alterResult)



					-- 插入结果数据表
					set @sqlstr = '

						insert into #result

						select p.dept_id as deptId, convert(varchar(10),p.card_date,120) as cardDate, 0 as in_or_out, 0 as sumUser,{1}
						from (
							select dept_id, shift_id, card_date, count(swipe_begin) qty
							from #data
							where isnull(swipe_begin,0) > 0
							group by dept_id, shift_id, card_date
						) t
						pivot (
							sum(qty)
							for shift_id IN ({2})
						) p

						union all

						select p.dept_id as deptId, convert(varchar(10),p.card_date,120) as cardDate, 1 as in_or_out, 0 as sumUser,{1}
						from (
							select dept_id, shift_id, card_date, count(swipe_end) qty
							from #data
							where isnull(swipe_end,0) > 0
							group by dept_id, shift_id, card_date
						) t
							pivot (
							sum(qty)
							for shift_id IN ({2})
						) p
					';


					set @sqlstr=replace(@sqlstr,'{1}',@lstr1)
					set @sqlstr=replace(@sqlstr,'{2}',@lstr2)
					set @lstrCount=replace(@lstrCount,'{1}',@lstr1)
					set @lstrCount=replace(@lstrCount,'{2}',@lstr2)
					exec sp_executesql @lstrCount, N'@count int output', @totalSize output


					print @sqlstr
					exec (@sqlstr)


					-- 获取列
					select @col_name_str += 'isnull(col_'+isnull(shift_bh,'') + ',0)+'
					from #data
					group by shift_bh

					set @col_name_str=left(@col_name_str,len(@col_name_str)-1)


					-- 执行更新结果表
					exec ('update #result set sumUser = ' + @col_name_str)

          -- 更新总计
					update a set a.sumUser =b.sumUser
					from #result a
					inner join (
						select deptId,cardDate,sum(sumUser) as sumUser from #result
						group by deptId,cardDate
					) b on a.deptId = b.deptId and a.cardDate = b.cardDate

					--查询结果
					select
						a.*,
						case a.in_or_out when 0 then '进' when 1 then '出' end as inOrOut,
						b.dep_no_add3 as deptNo,b.dep_name3 as deptName
					from #result a
					INNER JOIN view_bd_dep_level b on a.deptId = b.dep_serial
					order by a.cardDate desc,a.deptId,a.in_or_out



					 set @lstrCount= '
								select
									@count = count(0)
								from #result a
								INNER JOIN view_bd_dep_level b on a.deptId = b.dep_serial
						';


					print @lstrCount;
					exec sp_executesql @lstrCount, N'@count int output', @totalSize output
				END



		--  提交事务
			COMMIT TRAN

	--  结束异常捕捉
		END TRY

	--  异常处理
		BEGIN CATCH
			DECLARE @errStr VARCHAR(MAX) = ERROR_MESSAGE();
			IF XACT_STATE() <> 0
			BEGIN
			  ROLLBACK TRANSACTION;
			  RAISERROR (@errStr, 16, 1);
			END;
		END CATCH


--  清除临时表
	drop table #data;
	drop table #result;


-- 打开打印受影响行数
SET NOCOUNT OFF;
END;
